package dbLayer;

import modelLayer.Staff;
import java.util.List;
import java.util.ArrayList;
import java.sql.*;

public class DBStaff {
	
	private Connection con;
	
	public DBStaff () {
		this.con = DBConnection.getInstance().getDBcon();
	}
	
	public List<Staff> findStaffName (String name) {
		
		ResultSet results;
		List<Staff> matchingStaff = new ArrayList<>();
		
		try {
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Staff WHERE Name like ?");
			statement.setString(1, "%" + name + "%");
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			while (results.next()){
				Staff staff = new Staff ();
				staff = buildObject(results);
				System.out.println(staff);
				matchingStaff.add(staff);
			}
			statement.close();
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
		return matchingStaff;
		
	}
	
	public Staff findStaffID (int ID) {
		
		Staff staff = null;
		ResultSet results;
		
		try {
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Staff WHERE ID = ?");
			statement.setInt(1, ID);
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			while(results.next()) {
				staff = buildObject(results);
				System.out.println(staff);
			}
			statement.close();
		}
		catch (Exception e) {
			System.out.println("Exception: "+e);
		}
		
		return staff;
	}

	public void addStaff(Staff staff) {
		
		try {
			
			PreparedStatement statement = con.prepareStatement("INSERT INTO Staff VALUES (?,?,?)");
			statement.setString(1, staff.getName());
			statement.setInt(2, staff.getCprNo());
			statement.setString(3, staff.getEmail());
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: "+e);
		}
		
	}

	public List<Staff> allStaff() {
		
		ResultSet results;
		
		List<Staff> allStaff = new ArrayList<>();
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Staff");
			statement.setQueryTimeout(5);
			
			results = statement.executeQuery();
			
			while (results.next()) {
				
				Staff staff = new Staff();
				staff = buildObject(results);
				allStaff.add(staff);
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: "+e);
		}
		
		return allStaff;
		
	}

	public int highestID() {
		
		ResultSet results;
		
		int maxID = 0;
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT MAX(ID) FROM Staff");
			statement.setQueryTimeout(5);
			
			results = statement.executeQuery();
			
			while (results.next()) {
				
				maxID = results.getInt(1);
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: "+e);
		}
		
		return maxID;
		
	}

	public void updateStaff(Staff staff) {
		
		try {
			
			PreparedStatement statement = con.prepareStatement("UPDATE Staff SET Name = ?, CPRNo = ?, Email = ? WHERE ID = ?");
			statement.setString(1, staff.getName());
			statement.setInt(2, staff.getCprNo());
			statement.setString(3, staff.getEmail());
			statement.setInt(4, staff.getID());
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: "+e);
		}
		
	}

	public void removeStaff(int staffID) {
		
		try {
			
			PreparedStatement statement = con.prepareStatement("DELETE FROM Staff WHERE ID = ?");
			statement.setInt(1, staffID);
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: "+e);
		}
		
	}

	private Staff buildObject(ResultSet results) {
		
		Staff staff = null;
		
		try {
			staff = new Staff (results.getInt("ID"),
								results.getString("Name"),
								results.getInt("cprNo"),
								results.getString("Email"));
		}
		catch (Exception e) {
			System.out.println("Creation of the staff member caused exception: " + e);
		}
		
		return staff;
	}

}
